import { Router } from "express"
import { Ioracle, IcallbackFn } from "../../types"
import dayjs from "dayjs"
import type { formHeader, CustomAuditForm,FPCCustomForm } from "./type"
import { decrypt } from "../../utils/privateKey"
import { BindParameters } from "oracledb"
import oracle from "../../oracle"
import { sendMail } from "./mail"

//新表單插入一筆流程
async function initFlow(docid: string, username: string, week_reocrd: string) {
  return new Promise((resolve, reject) => {
    let sql = ` insert into xx_cf_customar_apply_flow values('${docid}',10,'Information Maintaince'`
    sql += `,'${username}','','','${username}',sysdate,'${week_reocrd}')`
    oracle
      .connChaos({ sql })
      .then((res) => {
        if (res.code === 200) {
          resolve({ code: 200, message: "success" })
        }
      })
      .catch(() => {
        reject({ code: 0, message: "database error" })
      })
  })
}

async function SendMail(docid: string, sendtype: string, comments: string) {
  return new Promise((resolve, reject) => {
    let sql = `select b.chinese_name,substr(b.account,9)||'@compeq.huizhou.gd.cn' email,current_flow from xx_cf_customar_apply_header a, user_info_n b`
    sql += ` where a.docid = '${docid}' and a.action_klm = b.account`
    //如果action_klm 為 CF168CQE
    sql += ` union `
    sql += ` select 'CF168CQE群組' chinese_name,'cf168@compeq.huizhou.gd.cn' email,10 current_flow from xx_cf_customar_apply_header`
    sql += ` where action_klm = 'xdomain\\cf168cqe' and docid = '${docid}' and current_flow = 10`
    oracle.connChaos({ sql }).then((res: any) => {
      let chinese_name = res.data[0].chinese_name
      let email = res.data[0].email
      let mailsource = "./mail/custom_ar/approve.ejs"
      if (sendtype === "Reject") {
        mailsource = "./mail/custom_ar/reject.ejs"
      }
      //送出為同意，並且流程已經被更新到了10階段
      if (sendtype === "Approve" && res.data[0].current_flow === 10) {
        mailsource = "./mail/custom_ar/maintance.ejs"
      }
      sendMail({
        mailTo: email as string,
        mailSubject: "Custom-AR系統通知郵件",
        mailHtml: mailsource,
        mailData: { chinese_name, email, comments },
      })
        .then(() => {
          resolve({ code: 200, message: "發送成功", data: [] })
        })
        .catch((err) => {
          reject({ code: 0, message: err })
        })
    })
  })
}

//修改表頭--當修改表單的時候會用到
async function UpdateSaveHeader(
  docid: string,
  username: string,
  week_record: string,
  status: string
) {
  return new Promise((resolve, reject) => {
    let sql = `update xx_cf_customar_apply_header set last_update_by = '${username}',last_update_time = sysdate`
    sql += ` ,status='${status}',action_klm='${username}'`
    sql += ` where docid = '${docid}' and week_record='${week_record}'`
    oracle
      .connChaos({ sql })
      .then((res) => {
        if (res.code === 200) {
          //儲存之後流程當中信息維護人的當前執行者也需要改變。為第二週的新流程了。
          sql = ` update xx_cf_customar_apply_flow set action_klm = '${username}' where docid = '${docid}' and week_record = '${week_record}' and flow = 10`
          oracle
            .connChaos({ sql })
            .then((res) => {
              if (res.code === 200) {
                resolve({ code: 200, message: "success" })
              }
            })
            .catch(() => {
              reject({ code: 0, message: "database error" })
            })
        }
      })
      .catch(() => {
        reject({ code: 0, message: "database error" })
      })
  })
}

//表單申請或者維護
export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  //處理邏輯--根據表單號是否為空，選擇申請表單還是維護表單。
  //申請--先獲取流水號，再新增表頭、表明細--根據表單類型選擇是客訴還是稽核、如果是新單據需要新增一筆初始流程
  //修改--由於不知道用戶會對表單進行何種操作，可能刪除多條又新增多條，導致序號變亂，處理起來很麻煩，所以統一採用先刪除所有表單，按照前端給的數據重新新增。
  router.post("/applySave", (req, _res) => {
    //參數處理
    const { formHeader, formDetail } = req.body
    const formHeadrParse: formHeader = JSON.parse(formHeader)
    const formDetailParse: Array<CustomAuditForm> = []
    JSON.parse(formDetail).forEach((element: CustomAuditForm) => {
      formDetailParse.push(element)
    })

    //新增一筆數據
    if (formHeadrParse.docid === "" || formHeadrParse.docid == null) {
      let date: string = dayjs().format("YYYYMMDD")
      let sql = `select xx_customar_apply_seqno('${date}')no, TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') week from dual`
      oracle
        .connChaos({ sql })
        .then((res: any) => {
          if (res.code === 200 && res.data[0].no !== "error") {
            //年周數
            let week_record = res.data[0].week
            formHeadrParse.docid = res.data[0].no
            formHeadrParse.username = decrypt(formHeadrParse.username)
            formHeadrParse.current_flow = 10
            //獲取單據號，新增數據--主表
            sql = ` insert into xx_cf_customar_apply_header values`
            sql += ` ('${formHeadrParse.docid}',${formHeadrParse.formtype},'${formHeadrParse.username}',sysdate,${formHeadrParse.current_flow}`
            sql += `,${
              formHeadrParse.current_flow < 30
                ? formHeadrParse.current_flow + 10
                : 10
            }`
            sql += `,'${formHeadrParse.username}'`
            sql += `,'${formHeadrParse.username}',sysdate,'${formHeadrParse.status}'`
            //記錄當前年周數
            sql += `,'${week_record}','')`

            oracle
              .connChaos({ sql })
              .then((res) => {
                //插入明細表
                if (res.code === 200) {
                  let bind: Array<BindParameters> = []
                  //客訴表
                  if (formHeadrParse.formtype === 6) {
                    sql = ` insert into xx_cf_customar_fpcdashboard values(:docid,(select TO_CHAR(SYSDATE, 'YYYY') || '.' ||TO_CHAR(SYSDATE, 'MM')  from dual)
                    ,:issue_title,:issue_failure_rate,:downstrean_node,:issue_description,:application
                    ,:lob,:board_name,:code,:apn,:revision,:aftd,to_date(:issue_opening_date,'yyyy-mm-dd'),to_date(:issue_closing_date,'yyyy-mm-dd')
                    ,to_date(:issue_occurrence,'yyyy-mm-dd'),:issue_build_stage,:severity,:rc_responsibility,(select to_char(sysdate,'YYYY') from dual),(select to_char(sysdate,'IW') from dual)
                    ,:this_week_update,:next_action,:check_point,:next_action_dri,:url,:seq,:week_record)`
                    formDetailParse.forEach((element: CustomAuditForm) => {
                      bind.push({
                        docid: formHeadrParse.docid,
                        issue_title:element.issue_title,
                        issue_failure_rate:element.issue_failure_rate,
                        downstrean_node:element.downstrean_node,
                        issue_description:element.issue_description,
                        application:element.application,
                        seq: element.seq,
                        lob: element.lob,
                        board_name:element.board_name,
                        code:element.code,
                        apn: element.apn,
                        revision:element.revision,
                        issue_opening_date:element.issue_opening_date,
                        issue_closing_date:element.issue_closing_date,
                        severity:element.severity,
                        rc_responsibility:element.rc_responsibility,
                        issue_occurrence:element.issue_occurrence,
                        this_week_update:element.this_week_update,
                        issue_build_stage:element.issue_build_stage,
                        check_point:element.check_point,
                        next_action_dri:element.next_action_dri,
                        next_action: element.next_action,
                        aftd: element.aftd,
                        url: element.url,
                        week_record: week_record,
                      });
                    });
                    oracle
                      .connChaos({ sql, isMulti: true, params: bind })
                      .then((res) => {
                        if (res.code === 200) {
                          initFlow(
                            formHeadrParse.docid,
                            formHeadrParse.username,
                            week_record
                          )
                            .then(() => {
                              _res.send({
                                code: 200,
                                message: "Save Successfully!",
                                data: {
                                  docid: formHeadrParse.docid,
                                },
                              })
                            })
                            .catch(() => {
                              _res.send({
                                code: 0,
                                message: "database error!",
                              })
                            })
                        }
                      })
                      .catch(() => {
                        _res.send({ code: 0, message: "database error!" })
                      })
                  }
                  //稽核表
                  else {
                    sql = `insert into xx_cf_customar_auditor_lines values (:docid,:seq,to_date(:occur_date,'YYYY-MM-DD'),:auditor,:department`
                    sql += `,:category,:lob,:project_code,:apn,:findings,:improvement_actions,:dri,to_date(:implemention_date,'YYYY-MM-DD'),:week_record)`
                    formDetailParse.forEach((element: CustomAuditForm) => {
                      //由於稽核表單中所有表單類型不一樣，傳過來的值為undefined,統一設置為空串
                      bind.push({
                        docid: formHeadrParse.docid,
                        seq: element.seq,
                        occur_date: element.occur_date,
                        auditor:
                          element.auditor !== undefined ? element.auditor : "",
                        department:
                          element.department !== undefined
                            ? element.department
                            : "",
                        category:
                          element.category !== undefined
                            ? element.category
                            : "",
                        lob: element.lob !== undefined ? element.lob : "",
                        project_code:
                          element.project_code !== undefined
                            ? element.project_code
                            : "",
                        apn: element.apn !== undefined ? element.apn : "",
                        findings: element.findings,
                        improvement_actions: element.improvement_action,
                        dri: element.dri,
                        implemention_date: element.implementation_date,
                        week_record: week_record,
                      })
                    })
                    oracle
                      .connChaos({ sql, isMulti: true, params: bind })
                      .then((res) => {
                        if (res.code === 200) {
                          //初始化流程
                          initFlow(
                            formHeadrParse.docid,
                            formHeadrParse.username,
                            week_record
                          )
                            .then(() => {
                              _res.send({
                                code: 200,
                                message: "Save Successfully!",
                                data: {
                                  docid: formHeadrParse.docid,
                                },
                              })
                            })
                            .catch(() => {
                              _res.send({
                                code: 0,
                                message: "database error!",
                              })
                            })
                        }
                      })
                      .catch(() => {
                        _res.send({ code: 0, message: "database error" })
                      })
                  }
                }
              })
              .catch(() => {
                _res.send({ code: 0, message: "database error" })
              })
          } else {
            _res.send({
              code: 0,
              message: "The application documents are overloaded",
            })
          }
        })
        .catch(() => {
          _res.send({ code: 0, message: "database error" })
        })
    }
    //資料維護
    else {
      //為方便操作。表明細全部刪除，再全部新增，完成表單的修改,最後對表頭進行修改
      //先處理表明細
      let sql = `select  TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') week from dual`
      oracle
        .connChaos({ sql })
        .then((result) => {
          formHeadrParse.username = decrypt(formHeadrParse.username)
          let week_record = result.data[0].week
          sql = ``
          //如果是下一週的維護，delete week_record 下一週沒有資料，等於無刪除，再新增即可。
          if (formHeadrParse.formtype === 6) {
            sql = `delete from xx_cf_customar_fpcdashboard where docid = '${formHeadrParse.docid}' and week_record = '${week_record}'`
          } else {
            sql = `delete from xx_cf_customar_auditor_lines where docid = '${formHeadrParse.docid}' and week_record = '${week_record}'`
          }
          oracle
            .connChaos({ sql })
            .then((res) => {
              let bind: Array<BindParameters> = []
              if (res.code === 200) {
                if (formHeadrParse.formtype === 6) {
                  sql = ` insert into xx_cf_customar_fpcdashboard values(:docid,(select TO_CHAR(SYSDATE, 'YYYY') || '.' ||TO_CHAR(SYSDATE, 'MM')  from dual)
                    ,:issue_title,:issue_failure_rate,:downstrean_node,:issue_description,:application
                    ,:lob,:board_name,:code,:apn,:revision,:aftd,to_date(:issue_opening_date,'yyyy-mm-dd'),to_date(:issue_closing_date,'yyyy-mm-dd')
                    ,to_date(:issue_occurrence,'yyyy-mm-dd'),:issue_build_stage,:severity,:rc_responsibility,(select to_char(sysdate,'YYYY') from dual),(select to_char(sysdate,'IW') from dual)
                    ,:this_week_update,:next_action,:check_point,:next_action_dri,:url,:seq,:week_record)`
                    formDetailParse.forEach((element: CustomAuditForm) => {
                      bind.push({
                        docid: formHeadrParse.docid,
                        issue_title:element.issue_title,
                        issue_failure_rate:element.issue_failure_rate,
                        downstrean_node:element.downstrean_node,
                        issue_description:element.issue_description,
                        application:element.application,
                        seq: element.seq,
                        lob: element.lob,
                        board_name:element.board_name,
                        code:element.code,
                        apn: element.apn,
                        revision:element.revision,
                        issue_opening_date:element.issue_opening_date,
                        issue_closing_date:element.issue_closing_date,
                        severity:element.severity,
                        rc_responsibility:element.rc_responsibility,
                        issue_occurrence:element.issue_occurrence,
                        this_week_update:element.this_week_update,
                        issue_build_stage:element.issue_build_stage,
                        check_point:element.check_point,
                        next_action_dri:element.next_action_dri,
                        next_action: element.next_action,
                        aftd: element.aftd,
                        url: element.url,
                        week_record: week_record,
                      });
                    });
                  oracle
                    .connChaos({ sql, isMulti: true, params: bind })
                    .then(() => {
                      UpdateSaveHeader(
                        formHeadrParse.docid,
                        formHeadrParse.username,
                        week_record as string,
                        formHeadrParse.status
                      )
                        .then(() => {
                          _res.send({
                            code: 200,
                            message: "Execute Successfully",
                            data: { docid: formHeadrParse.docid },
                          })
                        })
                        .catch(() => {
                          _res.send({ code: 0, message: "database error!" })
                        })
                    })
                    .catch(() => {
                      _res.send({ code: 0, message: "database error!" })
                    })
                }
                //稽核表
                else {
                  sql = `insert into xx_cf_customar_auditor_lines values (:docid,:seq,to_date(:occur_date,'YYYY-MM-DD'),:auditor,:department`
                  sql += `,:category,:lob,:project_code,:apn,:findings,:improvement_actions,:dri,to_date(:implemention_date,'YYYY-MM-DD'),:week_record)`
                  formDetailParse.forEach((element: CustomAuditForm) => {
                    //由於稽核表單中所有表單類型不一樣，傳過來的值為undefined,統一設置為空串
                    bind.push({
                      docid: formHeadrParse.docid,
                      seq: element.seq,
                      occur_date: element.occur_date,
                      auditor:
                        element.auditor !== undefined ? element.auditor : "",
                      department:
                        element.department !== undefined
                          ? element.department
                          : "",
                      category:
                        element.category !== undefined ? element.category : "",
                      lob: element.lob !== undefined ? element.lob : "",
                      project_code:
                        element.project_code !== undefined
                          ? element.project_code
                          : "",
                      apn: element.apn !== undefined ? element.apn : "",
                      findings: element.findings,
                      improvement_actions: element.improvement_action,
                      dri: element.dri,
                      implemention_date: element.implementation_date,
                      week_record: week_record,
                    })
                  })
                  oracle
                    .connChaos({ sql, isMulti: true, params: bind })
                    .then((res) => {
                      if (res.code === 200) {
                        UpdateSaveHeader(
                          formHeadrParse.docid,
                          formHeadrParse.username,
                          week_record as string,
                          formHeadrParse.status
                        )
                          .then(() => {
                            _res.send({
                              code: 200,
                              message: "Execute Successfully",
                              data: { docid: formHeadrParse.docid },
                            })
                          })
                          .catch(() => {
                            _res.send({ code: 0, message: "database error!" })
                          })
                      }
                    })
                    .catch(() => {
                      _res.send({ code: 0, message: "database error" })
                    })
                }
              }
            })
            .catch(() => {
              _res.send({ code: 0, message: "database error" })
            })
        })
        .catch(() => {
          _res.send({ code: 0, message: "database error" })
        })
    }
  })

  router.post("/approve", (req, _res) => {
    const { username, docid, comments, status } = req.body
    //首先獲取單號當前在哪個流程--執行流程修改SendFlow、修改表頭、最後發送郵件
    let sql = `begin xx_customar_sendflow('${docid}','${decrypt(
      username
    )}','${comments}','${status}');end;`
    oracle
      .connChaos({ sql })
      .then(() => {
        //發送郵件
        SendMail(docid, status, comments)
          .then(() => {
            _res.send({
              code: 200,
              message: "success",
              data: { docid: docid },
            })
          })
          .catch(() => {
            _res.send({
              code: 0,
              message: "email send error",
            })
          })
      })
      .catch((err) => {
        _res.send({ code: 0, message: err })
      })
  })

  router.post("/deleteDocid", (req, res) => {
    const { docid } = req.body
    let sql = `select formtype from xx_cf_customar_apply_header where docid = '${docid}'`
    oracle
      .connChaos({ sql })
      .then((result) => {
        const formtype = result.data[0].formtype
        if (formtype) {
          sql = `delete from xx_cf_customar_apply_header where docid = '${docid}'`
          oracle
            .connChaos({ sql })
            .then(() => {
              sql = `delete from xx_cf_customar_apply_flow where docid = '${docid}'`
              oracle
                .connChaos({ sql })
                .then(() => {
                  sql = `delete from xx_cf_customar_auditor_lines where docid = '${docid}'`
                  if (formtype === 6) {
                    sql = `delete from xx_cf_customar_fpcdashboard where docid = '${docid}'`
                  }
                  callbackFn(res, oracle.connChaos, { sql })
                })
                .catch(() => {
                  res.send({
                    code: 0,
                    message: "database error",
                  })
                })
            })
            .catch(() => {
              res.send({
                code: 0,
                message: "database error",
              })
            })
        } else {
          res.send({
            code: 0,
            message: "no such docid in database",
          })
        }
      })
      .catch(() => {
        res.send({
          code: 0,
          message: "no such docid",
        })
      })
  })

  router.post("/fpcDashboard",(req,res)=>{
    const {username,data} = req.body
    let date: string = dayjs().format("YYYYMMDD");
    let sql = `select xx_customar_apply_seqno1('${date}')no, TO_CHAR(SYSDATE, 'YYYY') year, TO_CHAR(SYSDATE, 'IW') week, TO_CHAR(sysdate,'MM')month from dual`;
    oracle.connChaos({sql}).then((result)=>{
       let bind:Array<BindParameters> = []
       JSON.parse(data).forEach((item:FPCCustomForm) => {
         item.update_year = Number(result.data[0].year)
         item.update_week = Number(result.data[0].week)
         item.no = dayjs().format("YYYY")+"."+dayjs().format("MM")
         item.docid = result.data[0].no.toString()
         bind.push(item)
       });
       sql = ` insert into xx_cf_customar_fpcdashboard values(:docid,:no,:issue_title,:issue_failure_rate,:downstrean_node,:issue_description,:application
       ,:lob,:board_name,:code,:apn,:revision,:aftd,to_date(:issue_opening_date,'yyyy-mm-dd'),to_date(:issue_closing_date,'yyyy-mm-dd')
       ,to_date(:issue_occurrence,'yyyy-mm-dd'),:issue_build_stage,:severity,:rc_responsibility,:update_year,:update_week,:this_week_update
       ,:next_action,:check_point,:next_action_dri,:url,'${decrypt(username)}',sysdate,:seq)`
       callbackFn(res,oracle.connChaos,{sql,isMulti:true,params:bind})
    }).catch(()=>{
      res.send({
        code: 0,
        message: "database error",
      });
    })
  })

  router.post("/fpcGetstaic",(_req,res)=>{
     let sql = " select substr(apn,0,9)apn,substr(apn,11) apnversion from xxcf_mil_pn_setup"
     callbackFn(res,oracle.connCtShopFloor,{sql})
  })

  router.post("/fpcGetinfo",(req,res)=>{
    const {apn} = req.body
    let sql = ` select application,lob,code,project_name from xxcf_mil_pn_setup where apn = '${apn}'`
    callbackFn(res,oracle.connCtShopFloor,{sql})
  })

  return router
}
